﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;

namespace MySqlManager
{
    /// <summary>
    /// Base class for all the MySQL queries
    /// </summary>
    public abstract class SqlQuery
    {
        /// <summary>
        /// Gets or sets the delegate invoked by the Execute and BeginExecute methods
        /// </summary>
        protected Action executeDelegate;
        /// <summary>
        /// Queue containing all the parameters added to this Sql query
        /// </summary>
        protected Queue<string> parameters;
        /// <summary>
        /// Instance of a MySql.Data.MySqlClient.MySqlCommand
        /// </summary>
        protected MySqlCommand mySqlCommand;
        /// <summary>
        /// Gets or sets the name of the table that will be affected by this MySQL query
        /// </summary>
        protected string tableName;
        /// <summary>
        /// Gets or sets the max amount of Rows from the MySQL database table that will be affected by this MySQL query
        /// </summary>
        protected int? limit;
        /// <summary>
        /// Queue containing all the Where statments used in this MySQL query
        /// </summary>
        private Queue<WhereStruct> whereParameters;
        /// <summary>
        /// Gets or set the next Where connector to append a Where statment
        /// </summary>
        private WhereConnector nextConnector;

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlQuery"/> class
        /// </summary>
        protected SqlQuery()
        {
            this.nextConnector = WhereConnector.None;
            this.parameters = new Queue<string>();
            this.whereParameters = new Queue<WhereStruct>();
            this.mySqlCommand = new MySqlCommand();
        }

        /// <summary>
        /// Selects the MySQL database table that will be affected by this MySQL query
        /// </summary>
        /// <param name="table">Name of the MySQL database table</param>
        public void SelectTable(string table)
        {
            this.tableName = table;
        }

        /// <summary>
        /// Creates a new Where statment with the 'Equal To' operator
        /// </summary>
        /// <param name="value">Object that will be compared to the database value</param>
        /// <param name="column">Datbase column containing the value that will be compared with the object parameter</param>
        public SqlQuery WhereIsEqual(object value, string column)
        {
            this.whereParameters.Enqueue(new WhereStruct() { Field = column, Operator = WhereOperation.Equal, Connector = this.nextConnector });
            this.mySqlCommand.Parameters.AddWithValue("?1" + column, value);
            return this;
        }

        /// <summary>
        /// Creates a new Where statment with the 'Different To' operator
        /// </summary>
        /// <param name="value">Object that will be compared to the database value</param>
        /// <param name="column">Datbase column containing the value that will be compared with the object parameter</param>
        public SqlQuery WhereIsDistinct(object value, string column)
        {
            this.whereParameters.Enqueue(new WhereStruct() { Field = column, Operator = WhereOperation.Distinct, Connector = this.nextConnector });
            this.mySqlCommand.Parameters.AddWithValue("?1" + column, value);
            return this;
        }

        /// <summary>
        /// Creates a new Where statment with the 'Greater Than' operator
        /// </summary>
        /// <param name="value">Object that will be compared to the database value</param>
        /// <param name="column">Datbase column containing the value that will be compared with the object parameter</param>
        public SqlQuery WhereIsGreaterThan(object value, string column)
        {
            this.whereParameters.Enqueue(new WhereStruct() { Field = column, Operator = WhereOperation.GreaterThan, Connector = this.nextConnector });
            this.mySqlCommand.Parameters.AddWithValue("?1" + column, value);
            return this;
        }

        /// <summary>
        /// Creates a new Where statment with the 'Less Than' operator
        /// </summary>
        /// <param name="value">Object that will be compared to the database value</param>
        /// <param name="column">Datbase column containing the value that will be compared with the object parameter</param>
        public SqlQuery WhereIsLessThan(object value, string column)
        {
            this.whereParameters.Enqueue(new WhereStruct()
            { 
                Field = column,
                Operator = WhereOperation.LessThan,
                Connector = this.nextConnector
            });
            this.mySqlCommand.Parameters.AddWithValue("?1" + column, value);
            return this;
        }

        /// <summary>
        /// Creates a new Where statment with the 'Greater Than Or Equal To' operator
        /// </summary>
        /// <param name="value">Object that will be compared to the database value</param>
        /// <param name="column">Datbase column containing the value that will be compared with the object parameter</param>
        public SqlQuery WhereIsEqualOrGreaterThan(object value, string column)
        {
            this.whereParameters.Enqueue(new WhereStruct() { Field = column, Operator = WhereOperation.GreaterOrEqualThan, Connector = this.nextConnector });
            this.mySqlCommand.Parameters.AddWithValue("?1" + column, value);
            
            return this;
        }

        /// <summary>
        /// Creates a new Where statment with the 'Less Than Or Equal To' operator
        /// </summary>
        /// <param name="value">Object that will be compared to the database value</param>
        /// <param name="column">Datbase column containing the value that will be compared with the object parameter</param>
        public SqlQuery WhereIsEqualOrLessThan(object value, string column)
        {
            this.whereParameters.Enqueue(new WhereStruct() { Field = column, Operator = WhereOperation.LessOrEqualThan, Connector = this.nextConnector });
            this.mySqlCommand.Parameters.AddWithValue("?1" + column, value);
            return this;
        }

        /// <summary>
        /// Appends an 'And' to use with the next MySQL where statment created
        /// </summary>
        public SqlQuery And
        {
            get
            {
                this.nextConnector = WhereConnector.And;
                return this;
            }
        }

        /// <summary>
        /// Appends an 'Or' to use with the next MySQL where statment created
        /// </summary>
        public SqlQuery Or
        {
            get
            {
                this.nextConnector = WhereConnector.Or;
                return this;
            }
        }

        /// <summary>
        /// Sets the max amount of Rows affected from the MySQL database table
        /// </summary>
        /// <param name="limit">Amount of Rows</param>
        /// <returns></returns>
        public SqlQuery SetLimit(int limit)
        {
            this.limit = limit;
            return this;
        }

        /// <summary>
        /// Execute the current SqlQuery synchronously
        /// </summary>
        public void Execute()
        {
            this.executeDelegate.Invoke();
        }

        /// <summary>
        /// Execute the current SqlQuery asynchronously
        /// </summary>
        public void BeginExecute()
        {
            this.executeDelegate.BeginInvoke((IAsyncResult result) => { executeDelegate.EndInvoke(result); }, null);
        }

        /// <summary>
        /// Execute the current SqlQuery synchronously
        /// </summary>
        /// <param name="callback">Method that will be invoked after this SqlQuery execution finishes</param>
        public void BeginExecute(AsyncCallback callback)
        {
            this.executeDelegate.BeginInvoke(callback, null);
        }

        /// <summary>
        /// Execute the current SqlQuery synchronously
        /// </summary>
        /// <param name="callback">Method that will be invoked after this SqlQuery execution finishes</param>
        /// <param name="object">Object that can be recovered to use after this SqlQuery execution finishes</param>
        public void BeginExecute(AsyncCallback callback, object @object)
        {
            this.executeDelegate.BeginInvoke(callback, @object);
        }

        /// <summary>
        /// Adds a new column name to the queue and adds the value to the MySqlCommand parameters
        /// </summary>
        /// <param name="column">Name of the column</param>
        /// <param name="value">Object that will be added to the MySqlCommand parameters</param>
        protected void AddParameter(string column, object value)
        {
            this.parameters.Enqueue(column);
            this.mySqlCommand.Parameters.AddWithValue("?" + column, value);
        }

        /// <summary>
        /// Append all the Where statments created in this SqlQuery
        /// </summary>
        /// <param name="cmd">StringBuilder containing the current MySQL command text</param>
        protected void AppendWhereStatments(StringBuilder cmd)
        {
            while (this.whereParameters.Count > 0)
            {
                WhereStruct where = this.whereParameters.Dequeue();
                switch (where.Connector)
                {
                    case WhereConnector.And:
                        cmd.Append(" And ");
                        break;
                    case WhereConnector.Or:
                        cmd.Append(" Or ");
                        break;
                    default:
                        cmd.Append(" Where ");
                        break;
                }

                cmd.Append(where.Field)
                .Append(where.Operator)
                .Append("?1")
                .Append(where.Field);
            }
        }
    }
}
